import { BusinessException } from '../../types/exceptions.js';
import { Pool as MySQLPool, ResultSetHeader } from 'mysql2/promise';
import { Pool as PgPool } from 'pg';
import {
  DatabaseOperations,
  DatabaseOperationsFactory,
  BaseMySQLOperations,
  BasePostgreSQLOperations,
} from '../../utils/database-operations.js';
import {
  SysFile,
  CreateSysFileDto,
  UpdateSysFileDto,
  SysFileQueryDto,
  FileUploadDto,
  FileUploadResponseDto,
  FileBatchDeleteDto,
  FileStatisticsDto,
} from '../../entities/system/SysFile.js';

// SysFile特定的数据库操作接口
interface SysFileOperations extends DatabaseOperations<SysFile> {
  getByUserId(userId: number): Promise<SysFile[]>;
  getByFileType(fileType: number): Promise<SysFile[]>;
  getByFileName(fileName: string): Promise<SysFile[]>;
  batchDelete(ids: number[]): Promise<boolean>;
  getFileStatistics(userId?: number): Promise<FileStatisticsDto>;
  cleanupExpiredFiles(expiredTime: number): Promise<number>;
}

// MySQL数据库操作实现
class MySQLOperations extends BaseMySQLOperations<SysFile> implements SysFileOperations {
  constructor(pool: MySQLPool) {
    super(pool);
  }

  async create(file: CreateSysFileDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_file (
                      user_id, file_name, file_mime, file_type, file_size,
                      file_ext, file_path, file_thumb, store_type, time, update_time
                  ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const params: (string | number)[] = [
      file.user_id as number,
      file.file_name as string,
      file.file_mime as string,
      file.file_type as number,
      file.file_size as number,
      file.file_ext as string,
      file.file_path as string,
      file.file_thumb as string,
      file.store_type as number,
      now,
      now,
    ];

    const [result] = await this.pool.execute(sql, params);
    return (result as ResultSetHeader).insertId;
  }

  async update(id: number, file: UpdateSysFileDto): Promise<boolean> {
    const updateFields: string[] = [];
    const params: (string | number)[] = [];

    Object.entries(file).forEach(([key, value]) => {
      if (value !== undefined && key !== 'id' && key !== 'time') {
        updateFields.push(`${key} = ?`);
        params.push(value as string | number);
      }
    });

    if (updateFields.length === 0) {
      return false;
    }

    updateFields.push('update_time = ?');
    params.push(Date.now());
    params.push(id);

    const sql = `UPDATE sys_file SET ${updateFields.join(', ')} WHERE id = ? AND disabled = 0`;
    const [result] = await this.pool.execute(sql, params);
    return (result as ResultSetHeader).affectedRows > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = 'UPDATE sys_file SET disabled = 1, update_time = ? WHERE id = ?';
    const [result] = await this.pool.execute(sql, [Date.now(), id]);
    return (result as ResultSetHeader).affectedRows > 0;
  }

  async getByUserId(userId: number): Promise<SysFile[]> {
    const sql = 'SELECT * FROM sys_file WHERE user_id = ? AND disabled = 0 ORDER BY time DESC';
    const [rows] = await this.pool.execute(sql, [userId]);
    return rows as SysFile[];
  }

  async getByFileType(fileType: number): Promise<SysFile[]> {
    const sql = 'SELECT * FROM sys_file WHERE file_type = ? AND disabled = 0 ORDER BY time DESC';
    const [rows] = await this.pool.execute(sql, [fileType]);
    return rows as SysFile[];
  }

  async getByFileName(fileName: string): Promise<SysFile[]> {
    const sql = 'SELECT * FROM sys_file WHERE file_name LIKE ? AND disabled = 0 ORDER BY time DESC';
    const [rows] = await this.pool.execute(sql, [`%${fileName}%`]);
    return rows as SysFile[];
  }

  async batchDelete(ids: number[]): Promise<boolean> {
    if (ids.length === 0) return true;

    const placeholders = ids.map(() => '?').join(',');
    const sql = `UPDATE sys_file SET disabled = 1, update_time = ? WHERE id IN (${placeholders})`;
    const params = [Date.now(), ...ids];

    const [result] = await this.pool.execute(sql, params);
    return (result as ResultSetHeader).affectedRows > 0;
  }

  async getFileStatistics(userId?: number): Promise<FileStatisticsDto> {
    let whereClause = 'WHERE disabled = 0';
    const params: number[] = [];

    if (userId !== undefined) {
      whereClause += ' AND user_id = ?';
      params.push(userId);
    }

    // 获取总文件数和总大小
    const totalSql = `SELECT COUNT(*) as total_files, SUM(file_size) as total_size FROM sys_file ${whereClause}`;
    const [totalResult] = await this.pool.execute(totalSql, params);
    const { total_files, total_size } = (
      totalResult as { total_files: number; total_size: number }[]
    )[0];

    // 获取按文件类型的统计
    const typeSql = `SELECT file_type, COUNT(*) as count, SUM(file_size) as size 
                     FROM sys_file ${whereClause} 
                     GROUP BY file_type ORDER BY file_type`;
    const [typeResult] = await this.pool.execute(typeSql, params);

    return {
      total_files: Number(total_files) || 0,
      total_size: Number(total_size) || 0,
      file_type_stats: typeResult as { file_type: number; count: number; size: number }[],
    };
  }

  async cleanupExpiredFiles(expiredTime: number): Promise<number> {
    const sql = 'UPDATE sys_file SET disabled = 1, update_time = ? WHERE time < ? AND disabled = 0';
    const [result] = await this.pool.execute(sql, [Date.now(), expiredTime]);
    return (result as ResultSetHeader).affectedRows;
  }

  async getById(id: number): Promise<SysFile | null> {
    const sql = 'SELECT * FROM sys_file WHERE id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [id]);
    const result = rows as SysFile[];
    return result.length > 0 ? result[0] : null;
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysFileQueryDto = {}
  ): Promise<{
    list: SysFile[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = ['disabled = 0'];
    const params: (string | number)[] = [];

    if (conditions.user_id !== undefined) {
      whereConditions.push('user_id = ?');
      params.push(conditions.user_id);
    }
    if (conditions.file_name) {
      whereConditions.push('file_name LIKE ?');
      params.push(`%${conditions.file_name}%`);
    }
    if (conditions.file_type !== undefined) {
      whereConditions.push('file_type = ?');
      params.push(conditions.file_type);
    }
    if (conditions.file_ext) {
      whereConditions.push('file_ext = ?');
      params.push(conditions.file_ext);
    }
    if (conditions.store_type !== undefined) {
      whereConditions.push('store_type = ?');
      params.push(conditions.store_type);
    }

    const whereClause = whereConditions.join(' AND ');
    const countSql = `SELECT COUNT(*) as total FROM sys_file WHERE ${whereClause}`;
    const [countResult] = await this.pool.execute(countSql, params);
    const total = (countResult as { total: number }[])[0].total;

    const offset = (page - 1) * pageSize;
    const listSql = `SELECT * FROM sys_file WHERE ${whereClause} ORDER BY time DESC LIMIT ? OFFSET ?`;
    const [rows] = await this.pool.execute(listSql, [...params, pageSize, offset]);

    return {
      list: rows as SysFile[],
      total,
      page,
      pageSize,
    };
  }
}

// PostgreSQL数据库操作实现
class PostgreSQLOperations extends BasePostgreSQLOperations<SysFile> implements SysFileOperations {
  constructor(pool: PgPool) {
    super(pool);
  }

  async create(file: CreateSysFileDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_file (
                      user_id, file_name, file_mime, file_type, file_size,
                      file_ext, file_path, file_thumb, store_type, time, update_time
                  ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) RETURNING id`;
    const params = [
      file.user_id,
      file.file_name,
      file.file_mime,
      file.file_type,
      file.file_size,
      file.file_ext,
      file.file_path,
      file.file_thumb,
      file.store_type,
      now,
      now,
    ];

    const result = await this.pool.query(sql, params);
    return result.rows[0].id;
  }

  async update(id: number, file: UpdateSysFileDto): Promise<boolean> {
    const updateFields: string[] = [];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    Object.entries(file).forEach(([key, value]) => {
      if (value !== undefined && key !== 'id' && key !== 'time') {
        updateFields.push(`${key} = $${paramIndex}`);
        params.push(value as string | number);
        paramIndex++;
      }
    });

    if (updateFields.length === 0) {
      return false;
    }

    updateFields.push(`update_time = $${paramIndex}`);
    params.push(Date.now());
    paramIndex++;

    params.push(id);
    const sql = `UPDATE sys_file SET ${updateFields.join(', ')} WHERE id = $${paramIndex} AND disabled = 0`;
    const result = await this.pool.query(sql, params);
    return result.rowCount !== null && result.rowCount > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = 'UPDATE sys_file SET disabled = 1, update_time = $1 WHERE id = $2';
    const result = await this.pool.query(sql, [Date.now(), id]);
    return result.rowCount !== null && result.rowCount > 0;
  }

  async getByUserId(userId: number): Promise<SysFile[]> {
    const sql = 'SELECT * FROM sys_file WHERE user_id = $1 AND disabled = 0 ORDER BY time DESC';
    const result = await this.pool.query(sql, [userId]);
    return result.rows;
  }

  async getByFileType(fileType: number): Promise<SysFile[]> {
    const sql = 'SELECT * FROM sys_file WHERE file_type = $1 AND disabled = 0 ORDER BY time DESC';
    const result = await this.pool.query(sql, [fileType]);
    return result.rows;
  }

  async getByFileName(fileName: string): Promise<SysFile[]> {
    const sql =
      'SELECT * FROM sys_file WHERE file_name ILIKE $1 AND disabled = 0 ORDER BY time DESC';
    const result = await this.pool.query(sql, [`%${fileName}%`]);
    return result.rows;
  }

  async batchDelete(ids: number[]): Promise<boolean> {
    if (ids.length === 0) return true;

    const client = await this.pool.connect();
    try {
      await client.query('BEGIN');

      const now = Date.now();
      for (const id of ids) {
        await client.query('UPDATE sys_file SET disabled = 1, update_time = $1 WHERE id = $2', [
          now,
          id,
        ]);
      }

      await client.query('COMMIT');
      return true;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  async getFileStatistics(userId?: number): Promise<FileStatisticsDto> {
    let whereClause = 'WHERE disabled = 0';
    const params: number[] = [];
    let paramIndex = 1;

    if (userId !== undefined) {
      whereClause += ` AND user_id = $${paramIndex}`;
      params.push(userId);
      paramIndex++;
    }

    // 获取总文件数和总大小
    const totalSql = `SELECT COUNT(*) as total_files, SUM(file_size) as total_size FROM sys_file ${whereClause}`;
    const totalResult = await this.pool.query(totalSql, params);
    const { total_files, total_size } = totalResult.rows[0];

    // 获取按文件类型的统计
    const typeSql = `SELECT file_type, COUNT(*) as count, SUM(file_size) as size 
                     FROM sys_file ${whereClause} 
                     GROUP BY file_type ORDER BY file_type`;
    const typeResult = await this.pool.query(typeSql, params);

    return {
      total_files: Number(total_files) || 0,
      total_size: Number(total_size) || 0,
      file_type_stats: typeResult.rows.map(row => ({
        file_type: row.file_type,
        count: Number(row.count),
        size: Number(row.size),
      })),
    };
  }

  async cleanupExpiredFiles(expiredTime: number): Promise<number> {
    const sql =
      'UPDATE sys_file SET disabled = 1, update_time = $1 WHERE time < $2 AND disabled = 0';
    const result = await this.pool.query(sql, [Date.now(), expiredTime]);
    return result.rowCount || 0;
  }

  async getById(id: number): Promise<SysFile | null> {
    const sql = 'SELECT * FROM sys_file WHERE id = $1 AND disabled = 0';
    const result = await this.pool.query(sql, [id]);
    return result.rows.length > 0 ? result.rows[0] : null;
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysFileQueryDto = {}
  ): Promise<{
    list: SysFile[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = ['disabled = 0'];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    if (conditions.user_id !== undefined) {
      whereConditions.push(`user_id = $${paramIndex}`);
      params.push(conditions.user_id);
      paramIndex++;
    }
    if (conditions.file_name) {
      whereConditions.push(`file_name ILIKE $${paramIndex}`);
      params.push(`%${conditions.file_name}%`);
      paramIndex++;
    }
    if (conditions.file_type !== undefined) {
      whereConditions.push(`file_type = $${paramIndex}`);
      params.push(conditions.file_type);
      paramIndex++;
    }
    if (conditions.file_ext) {
      whereConditions.push(`file_ext = $${paramIndex}`);
      params.push(conditions.file_ext);
      paramIndex++;
    }
    if (conditions.store_type !== undefined) {
      whereConditions.push(`store_type = $${paramIndex}`);
      params.push(conditions.store_type);
      paramIndex++;
    }

    const whereClause = whereConditions.join(' AND ');
    const countSql = `SELECT COUNT(*) as total FROM sys_file WHERE ${whereClause}`;
    const countResult = await this.pool.query(countSql, params);
    const total = Number(countResult.rows[0].total);

    const offset = (page - 1) * pageSize;
    const listSql = `SELECT * FROM sys_file WHERE ${whereClause} ORDER BY time DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    const listResult = await this.pool.query(listSql, [...params, pageSize, offset]);

    return {
      list: listResult.rows,
      total,
      page,
      pageSize,
    };
  }
}

export class SysFileService {
  private dbOperations: SysFileOperations;

  constructor() {
    this.dbOperations = DatabaseOperationsFactory.create<SysFileOperations>(
      MySQLOperations,
      PostgreSQLOperations
    );
  }

  async create(file: CreateSysFileDto): Promise<number> {
    return await this.dbOperations.create(file);
  }

  async update(id: number, file: UpdateSysFileDto): Promise<boolean> {
    return await this.dbOperations.update(id, file);
  }

  async delete(id: number): Promise<boolean> {
    const existing = await this.dbOperations.getById(id);
    if (!existing) {
      throw new BusinessException('文件不存在', 404);
    }
    return await this.dbOperations.delete(id);
  }

  async getByUserId(userId: number): Promise<SysFile[]> {
    return await this.dbOperations.getByUserId(userId);
  }

  async getByFileType(fileType: number): Promise<SysFile[]> {
    return await this.dbOperations.getByFileType(fileType);
  }

  async getByFileName(fileName: string): Promise<SysFile[]> {
    return await this.dbOperations.getByFileName(fileName);
  }

  async batchDelete(data: FileBatchDeleteDto): Promise<boolean> {
    if (data.ids.length === 0) {
      throw new BusinessException('请选择要删除的文件', 400);
    }
    return await this.dbOperations.batchDelete(data.ids);
  }

  async getFileStatistics(userId?: number): Promise<FileStatisticsDto> {
    return await this.dbOperations.getFileStatistics(userId);
  }

  async cleanupExpiredFiles(expiredTime: number): Promise<number> {
    return await this.dbOperations.cleanupExpiredFiles(expiredTime);
  }

  async getById(id: number): Promise<SysFile | null> {
    return await this.dbOperations.getById(id);
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    query: SysFileQueryDto = {}
  ): Promise<{ total: number; list: SysFile[]; page: number; pageSize: number }> {
    return await this.dbOperations.list(page, pageSize, query);
  }

  // 文件上传处理方法
  async uploadFile(
    uploadData: FileUploadDto,
    filePath: string,
    thumbPath?: string
  ): Promise<FileUploadResponseDto> {
    const fileData: CreateSysFileDto = {
      user_id: uploadData.user_id,
      file_name: uploadData.file_name,
      file_mime: uploadData.file_mime,
      file_type: uploadData.file_type,
      file_size: uploadData.file_size,
      file_ext: uploadData.file_ext,
      file_path: filePath,
      file_thumb: thumbPath || '',
      store_type: uploadData.store_type || 1,
      time: Date.now(),
      update_time: Date.now(),
      disabled: 0,
    };

    const id = await this.create(fileData);

    return {
      id,
      file_name: uploadData.file_name,
      file_path: filePath,
      file_thumb: thumbPath,
      file_size: uploadData.file_size,
      file_url: this.generateFileUrl(filePath, uploadData.store_type || 1),
    };
  }

  // 生成文件访问URL
  private generateFileUrl(filePath: string, storeType: number): string {
    // 根据存储类型生成不同的URL
    if (storeType === 1) {
      // 文件系统存储
      return `/files/${filePath}`;
    } else if (storeType === 2) {
      // MinIO存储
      return `/minio/${filePath}`;
    }
    return filePath;
  }
}
